import pandas as pd
from sqlalchemy import create_engine
from urllib.parse import quote_plus

stations = [
    'Aotizhongxin',
    'Changping',
    'Dingling',
    'Dongsi',
    'Guanyuan',
    'Gucheng',
    'Huairou',
    'Nongzhanguan',
    'Shunyi',
    'Tiantan',
    'Wanliu',
    'Wanshouxigong'
]

engine = create_engine(f'mysql+pymysql://root:{quote_plus("123456")}@127.0.0.1:3306/air-quality')

# 定义你的SQL查询
sql_query = 'select * from t_prsa_data where station = "%s" order by `year`, `month`, `day`, `hour`'

for station in stations:
    df = pd.read_sql_query(sql_query % station, engine)

    df['pm25_24_avg'] = df['pm25'].rolling(window=24, min_periods=1).mean()
    df['pm10_24_avg'] = df['pm10'].rolling(window=24, min_periods=1).mean()
    df['o3_8_avg'] = df['o3'].rolling(window=8, min_periods=1).mean()

    air_quality_hour_df = df[
        ['year', 'month', 'day', 'hour', 'pm25', 'pm25_24_avg', 'pm10', 'pm10_24_avg', 'so2', 'no2', 'co', 'o3',
         'o3_8_avg', 'station']]

    air_quality_hour_df.to_sql(name='t_air_quality_hour', con=engine, if_exists='append', index=False)
